package functest;
import java.sql.*;
import java.util.*;
import java.util.regex.*;
public class FindAPrint {

	public static final String DBDriver="com.mysql.jdbc.Driver";
	public static final String DBURl="jdbc:mysql://localhost:3306/hbpw?useUnicode=true&characterEncoding=utf-8&useSSL=false";
	public static final String DBusername="hbpwuser",DBpw="LFFLXY";
    public static final String[] weekday = new String[]{"日","一","二","三","四","五","六"};

	
	public Connection conDB()
	{
		 try{
                Connection con=null;
	            Class.forName(DBDriver);
	        //    System.out.println("connect successfully");
	            return DriverManager.getConnection(DBURl,DBusername,DBpw);
	            
	        }catch (Exception e) {
	            e.printStackTrace();
	          //  System.out.println("connection failed");
	            return null;
	        }
	}
	public void delcon(Connection con) throws SQLException
	{
		con.close();
		//System.out.println("delete successfully");
	}

	//查找数据库中的城市；返回所有城市名称
	public List<String> Findcitys() throws SQLException
	{
        Connection con=conDB();
        Statement stat=con.createStatement();
		String sql="select CS_MC from cs;";
		List<String> list= new ArrayList<String>();
		ResultSet re=stat.executeQuery(sql);
		while(re.next())
		{	String cityname=re.getString("CS_MC");
			list.add(cityname);
		}
		delcon(con);
		return list;
	}
	
	//查找城市代码；输入城市名称，返回城市代码
	public String Findcity(String cityname) throws SQLException
	{
	    String citydm=null;
		Connection con=conDB();
        Statement stat=con.createStatement();
		stat=con.createStatement();
		String sql="select CS_DM from cs where CS_MC='"+cityname+"';";
		ResultSet re=stat.executeQuery(sql);
		while(re.next())
		{
			citydm=re.getString("CS_DM");
		}
        delcon(con);
		return citydm;
	}

	//查找某个城市的机场；输入城市名称,返回所有机场名称
	public List<String> Findjcs(String cityname) throws SQLException
	{	List<String> list =new ArrayList<String>();
		Connection con=conDB();
        Statement stat=con.createStatement();
		String citydm=Findcity(cityname);
		String sql="select JC_MC from jc where CS_DM="+citydm+";";
		ResultSet re=stat.executeQuery(sql);
		while(re.next())
		{	list.add(re.getString("JC_MC"));
		}
        delcon(con);
		return list;
					
	}

    //查找某个城市的机场；输入城市ID,返回所有机场名称
    public List<String> Findjcbyid(String cityid) throws SQLException
    {	List<String> list =new ArrayList<String>();
        Connection con=conDB();
        Statement stat=con.createStatement();
        String sql="select JC_MC from jc where CS_DM="+cityid+";";
        ResultSet re=stat.executeQuery(sql);
        while(re.next())
        {	list.add(re.getString("JC_MC"));
        }
        delcon(con);
        return list;

    }

    //查找所有符合条件的机场名称；输入一个机场名称，返回所有非该机场的名称
    public List<String> Findalljcname(String jcname) throws SQLException
    {	List<String> list =new ArrayList<String>();
        Connection con=conDB();
        Statement stat=con.createStatement();
        String sql="select JC_MC from jc where JC_MC<>'"+jcname+"';";
        ResultSet re=stat.executeQuery(sql);
        while(re.next())
        {	list.add(re.getString("JC_MC"));
        }
        delcon(con);
        return list;

    }

    //查找某个客户名称；输入客户ID,返回客户名称
    public String Findgkbyid(String gkid) throws SQLException
    {
        Connection con=conDB();
        Statement stat=con.createStatement();
        String GK_XM=null;
        String sql="select GK_XM from gk where GK_BH='"+gkid+"';";
        ResultSet re=stat.executeQuery(sql);
        while(re.next())
        {
            GK_XM=re.getString("GK_XM");
        }
        delcon(con);
        return GK_XM;
    }
	//查找机场代码;输入机场名称，返回机场代码
	public String Findjc(String jcname) throws SQLException
	{	
		Connection con=conDB();
        Statement stat=con.createStatement();
		String JC_DM=null;
		String sql="select JC_DM from jc where JC_MC='"+jcname+"';";
		ResultSet re=stat.executeQuery(sql);
		while(re.next())
		{
			JC_DM=re.getString("JC_DM");
		}
        delcon(con);
		return JC_DM;
		
	}

    //查找机场名称;输入机场代码，返回机场名称
    public String Findjcname(String jcid) throws SQLException
    {
        Connection con=conDB();
        Statement stat=con.createStatement();
        String JC_MC=null;
        String sql="select JC_MC from jc where JC_DM='"+jcid+"';";
        ResultSet re=stat.executeQuery(sql);
        while(re.next())
        {
            JC_MC=re.getString("JC_MC");
        }
        delcon(con);
        return JC_MC;
    }

    //查找出合适的航线,参数为起始、到达机场名称
    public List<Map<String,String>> Findhx (String stjcname,String enjcname) throws SQLException
    {
        Connection con=conDB();
        Statement stat=con.createStatement();
        String stjc=this.Findjc(stjcname);
        String enjc=this.Findjc(enjcname);
        String sql="select a.HB_HBH as HBH,c.HB_FJJX as FJJX," //HBH航班号，FJJX飞机机型
                + "a.HD_SXH as SHDH,b.HD_SXH as EHDH,c.HB_HBHCAP as Ftime ," //SHDH起始航线段号，EHDH结束航线号，Ftime时间安排(每周)
                + "b.HD_JJCJG as JJCJG,b.HD_TDCJG as TDCJG "	//JJCJG经济舱价格，TDCJG头等舱价格
                + "from hd a,hd b,hb c where a.HB_HBH=b.HB_HBH and a.HB_HBH=c.HB_HBH and a.JC_START="+stjc+" and b.JC_END="+enjc+" and a.HD_SXH<=b.HD_SXH;";
        List< Map<String,String>> list =new ArrayList<Map<String,String>>();

        ResultSet re=stat.executeQuery(sql);
        if(re.next())
        {	re.previous();
            while(re.next())
            {	Map<String,String> map=new HashMap<String,String>();
                String HBH	=re.getString("HBH");
                String FJJX	=re.getString("FJJX");
                String SHDH	=re.getString("SHDH");
                String EHDH	=re.getString("EHDH");
                String Ftime=re.getString("Ftime");
                String JJCJG=re.getString("JJCJG");
                String TDCJG=re.getString("TDCJG");
                map.put("HBH", HBH);
                map.put("FJJX", FJJX);
                map.put("SHDH",SHDH);
                map.put("EHDH", EHDH);
                map.put("Ftime",Ftime);
                map.put("JJCJG",JJCJG);
                map.put("TDCJG",TDCJG);
                map.put("stjcname",stjcname);
                map.put("enjcname",enjcname);
                list.add(map);
            }
        }
        delcon(con);
        return list;
    }
	
	//删除时间不符的航班
	public List<Map<String,String>> checktime(List<Map<String,String>> list,String month,String day)
	{	if(list!=null)
		{	Iterator<Map<String,String>> iterator =list.iterator();
			while(iterator.hasNext())
			{
				Map<String,String> map=iterator.next();
				String Ftime=map.get("Ftime");
				if(!fixtime(Ftime,month,day))
				{
				    System.out.print("没有合适的时间,移除\n");
					iterator.remove();
				}
				else
                {
                    map.put("month",month);
                    map.put("day",day);
                }

			}
		
		}
		return list;
	}
	
	//确认周几；输入月，日，返回星期
	public int getWeek(String month,String day)
	{	int[] DOM={31,28,31,30,31,30,31,31,30,31,30,31};
		int days = 0;
		for(int i=0;i<Integer.parseInt(month)-1;i++)
		{
			days+=DOM[i];
		}
		int date=(Integer.parseInt(day)+days-1)%7;
		return date;
	}
	
	//判断时间是否合理;输入航班飞行时间，用户出发月，日
	public boolean fixtime(String Ftime,String month,String day)
	{	int week=getWeek(month,day);	
		if((Ftime.substring(week,week+1)).equals("1"))
			return true;
		else 
			return false;
	}

	//切割01时间字符串到中文字符串
	public String time2string(String Ftime)
    {
        String timelist = "";
        for (int i = 0; i < 7; i ++){
            if (Ftime.substring(i,i+1).equals("1")){
                timelist += weekday[i] + ",";
            }
        }
        timelist = timelist.substring(0,timelist.length()-1);
        return timelist;
    }

    //中文字符串到时间01字符串
    public String[] string2time(String chinesetime){
	    String[] timearray = chinesetime.split(",");
	    for (int i = 0; i < timearray.length; i ++){
	        timearray[i] = Arrays.binarySearch(weekday,timearray[i]) + "";
        }
	    return timearray;
    }

    //合理输入校验
    public static boolean isillgal(String company) {
        String regEx = "[0123456789_`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。，、？]|\n|\r|\t";
        Pattern p = Pattern.compile(regEx);
        Matcher m = p.matcher(company);
        if (m.find()) {
            return true;
        }
        return false;
    }

	//找到所有机场和对应城市
	public List<Map<String,String>> Findalljc() throws SQLException
	{	
		List<Map<String,String>> list =new ArrayList<Map<String,String>>();
		Connection con=conDB();
        Statement stat=con.createStatement();
		String sql="select JC_MC,CS_MC from jc,cs where jc.CS_DM=cs.CS_DM;";
		ResultSet re=stat.executeQuery(sql);
		while(re.next())	
		{	Map<String,String> map=new HashMap<String,String>();
			map.put(re.getString("CS_MC"), re.getString("JC_MC"));
			list.add(map);
		}
        delcon(con);
        return list;
	}


    //查找数据库中的售票信息；返回所有票务信息
    public List<Map<String,String>> Findspxx () throws SQLException
    {
        Connection con=conDB();
        Statement stat=con.createStatement();

        String sql="select * from spxx;";//起飞日期，价格
        List< Map<String,String>> list =new ArrayList<Map<String,String>>();

        ResultSet re=stat.executeQuery(sql);
        if(re.next())
        {	re.previous();
            while(re.next())
            {	Map<String,String> map=new HashMap<String,String>();
                String tkid	=re.getString("tkid");
                String clientid = re.getString("GK_BH");
                String clientname = Findgkbyid(re.getString("GK_BH"));
                String flightid	=re.getString("HB_HBH");
                String startport = Findjcname(re.getString("SPXX_CFJC"));
                String endport	=Findjcname(re.getString("SPXX_DDJC"));
                String classname = re.getString("SPXX_CW");
                String startdate = re.getString("SPXX_RQ");
                String price = re.getString("SPXX_JG");
                map.put("tkid", tkid);
                map.put("clientid", clientid);
                map.put("clientname", clientname);
                map.put("flightid", flightid);
                map.put("startport",startport);
                map.put("endport", endport);
                map.put("class",classname);
                map.put("date", startdate);
                map.put("price", price);
                list.add(map);
            }
        }
        delcon(con);
        return list;
    }

    //查找数据库中的航班信息；返回所有航班信息
    public List<Map<String,String>> Findallhb () throws SQLException
    {
        Connection con=conDB();
        Statement stat=con.createStatement();

        String sql="select * from hb;";
        List<Map<String,String>> list =new ArrayList<Map<String,String>>();

        ResultSet re=stat.executeQuery(sql);
        if(re.next())
        {	re.previous();
            while(re.next())
            {	Map<String,String> map=new HashMap<String,String>();
                String hbid	=re.getString("HB_HBH");
                String planeid	=re.getString("HB_FJJX");
                String weekplan = time2string(re.getString("HB_HBHCAP"));
                String topseat	= re.getString("HB_TDCS");
                String ecoseat = re.getString("HB_JJCS");
                map.put("hbid", hbid);
                map.put("planeid", planeid);
                map.put("weekplan",weekplan);
                map.put("topseat", topseat);
                map.put("ecoseat",ecoseat);
                list.add(map);
            }
        }
        delcon(con);
        return list;
    }
    //查找航班对应航段信息；
    public List<Map<String,String>> Findallhd (String hbh) throws SQLException
    {
        Connection con=conDB();
        Statement stat=con.createStatement();

        String sql="select * from hd where HB_HBH = '" + hbh +"';";
        List<Map<String,String>> list =new ArrayList<Map<String,String>>();

        ResultSet re=stat.executeQuery(sql);
        if(re.next())
        {	re.previous();
            while(re.next())
            {	Map<String,String> map=new HashMap<String,String>();
                String sortid	=re.getString("HD_SXH");
                String startport = Findjcname(re.getString("JC_START"));
                String endport	= Findjcname(re.getString("JC_END"));
                String topprice = re.getString("HD_TDCJG");
                String ecoprice = re.getString("HD_JJCJG");
                map.put("hbname",hbh);
                map.put("sortid",sortid);
                map.put("startport", startport);
                map.put("endport",endport);
                map.put("topprice", topprice);
                map.put("ecoprice",ecoprice);
                list.add(map);
            }
        }
        delcon(con);
        return list;
    }
	
/*
public static void main(String []args) throws SQLException
    {
		FindAPrint test =new FindAPrint();

		//测试fixtime
		String city="北京";
		String jca="北京首都机场";
		String jcb="杭州萧山机场";

		System.out.print(test.Findcitys()+"1\n");

		//测试findjcname
		System.out.print(test.Findjcs(city)+"2\n");

		//测试Findjc
		String jcadm=test.Findjc(jca);
		String jcbdm=test.Findjc(jcb);
		System.out.print(jcadm+" "+jcbdm+"3\n");

		//测试findhx;
		List <Map<String,String>> listmap=new ArrayList<Map<String,String>>();
		listmap=test.Findhx(jcadm, jcbdm);
		System.out.print(listmap+"4\n");

		//测试cherktime
		listmap=test.cherktime(listmap,"1","1");
		System.out.print(listmap);

	}
*/
	
}
